CREATE OR REPLACE PACKAGE BODY pkg_user_info
IS


   PROCEDURE modify_user_alert_num  (
       p_flag          OUT   PLS_INTEGER
	  ,p_ret_msg       OUT   VARCHAR2 
	  ,p_data_msg      OUT   VARCHAR2   
      ,p_user_id             PLS_INTEGER
      ,p_old_alert_num       VARCHAR2   
      ,p_new_alert_num       VARCHAR2   
	  ,p_interface           VARCHAR2 DEFAULT NULL 
      )
	IS
	    v_flag           PLS_INTEGER := 0 ;
		v_msg            VARCHAR2(4000);	
		v_ct             PLS_INTEGER := 0 ;
		v_interface_msg VARCHAR2(50) :=  CASE WHEN p_interface IS NULL THEN NULL ELSE ',"interface":"'||p_interface||'"' END ;
		
		v_data_type    dev_outgoing_log.data_type%TYPE  := '2'; --  
		            -- xxxxxxxxxxx,xxxxxxxxxxx   -- 11 位电话号码
		            -- 类型为2的数据定义为： （1），（2）， 如果电话号码全为0， 则该报警电话无效。
	BEGIN
	
		--pkg_debug.debug_log( $$PLSQL_UNIT, $$PLSQL_LINE,
		--					'p_user_id :'||p_user_id|| 
		--					'old_alert_num :'||p_old_alert_num|| 
		--					'new_alert_num :'||p_new_alert_num 
		--					);
	
		UPDATE user_info 
		SET    alert_number  = p_new_alert_num -- decode( p_new_alert_num, 0, NULL, p_new_alert_num)
		WHERE  user_id    = p_user_id 
		AND    nvl(alert_number,0)  = p_old_alert_num;
	
		v_ct := SQL%ROWCOUNT;
		
		IF v_ct = 0 THEN 
			ROLLBACK;
			p_flag := 1;
			
			p_ret_msg := '用户或报警电话不存在(user_id:'||p_user_id||',alert_num:'||p_old_alert_num||')';
			p_data_msg  := '{"flag": '||p_flag||v_interface_msg||',"message": "'|| p_ret_msg||'" }';
		ELSE 
			INSERT INTO dev_outgoing_log (
									 dev_outgoing_logid  
									,device_sn           
									,device_id           
									,created_by          
									,data_type           
									,outgoing_data       
			)
			SELECT dev_outgoing_log_seq.nextval 
			     , device_sn
				 , device_id
				 , 'App-User_info'
				 , v_data_type
				 , to_char(u.account, 'fm00000000000')||','|| to_char(nvl(u.alert_number,0), 'fm00000000000') 
			FROM device_list d
			INNER JOIN pond_info p USING (pond_id)
			INNER JOIN user_info u USING (user_id)
			WHERE user_id = p_user_id 
			 --AND  DECODE ( p_old_alert_num, p_new_alert_num, 1, 0) = 0   --- only when number changed , then we send it to devices 
			 ;
			
			COMMIT;
			p_flag := 0;
			
			p_ret_msg := '成功修改用户报警电话信息';
			p_data_msg  := '{"flag": '||p_flag||v_interface_msg||',"message": "'|| p_ret_msg||'" }';
		
		END IF;
		
			pkg_debug.debug_log( $$PLSQL_UNIT, $$PLSQL_LINE,
								'p_flag :'||p_flag  ||
							   ',p_ret_mag :'||p_ret_msg  
								);
		RETURN;
	EXCEPTION
			
		WHEN OTHERS  THEN 
			p_flag := -2 ;
			p_ret_msg := '修改用户报警电话失败，未知错误：'|| replace( sqlerrm(), '"', '');
			p_data_msg := NULL;
			p_data_msg  := '{"flag": '||p_flag||v_interface_msg||',"message": "'|| p_ret_msg||'" }';
				pkg_debug.debug_log( $$PLSQL_UNIT, $$PLSQL_LINE,
								'p_flag :'||p_flag  ||
							   ',p_ret_mag :'||p_ret_msg  
								);		
	END modify_user_alert_num  ;


   PROCEDURE modify_user_addr  (
       p_flag          OUT   PLS_INTEGER
	  ,p_ret_msg       OUT   VARCHAR2 
	  ,p_data_msg      OUT   VARCHAR2   
      ,p_user_id             PLS_INTEGER
      ,p_addr_id             PLS_INTEGER
      ,p_country_id          PLS_INTEGER
      ,p_street_info         VARCHAR2   
      )
	IS
	    v_flag         PLS_INTEGER := 0 ;
		v_msg          VARCHAR2(4000);	
		v_ct           PLS_INTEGER := 0 ;
		v_country_id   PLS_INTEGER := NVL( p_country_id, 0);
	
	BEGIN
		SELECT COUNT(1) 
			INTO v_ct 
		FROM addr_code 
		WHERE addr_id  = p_addr_id; 
		
		IF v_ct = 0 THEN 
			p_flag := 1 ;
			p_ret_msg := '地址不在存（addr_id:'||p_addr_id||')';
			p_data_msg  := '{"flag": '||p_flag||',"message": "'|| p_ret_msg||'" }';
			return ;
		END IF;
		
		SELECT COUNT(1) 
			INTO v_ct 
		FROM geo_countries 
		WHERE country_id  = p_country_id; 
		IF v_ct = 0 and v_country_id > 0 THEN 
			p_flag := 1 ;
			p_ret_msg := '国家编码不在存（country_id:'||p_country_id||')';
			p_data_msg  := '{"flag": '||p_flag||',"message": "'|| p_ret_msg||'" }';
			return ;
		END IF;
		
		UPDATE user_info 
		SET    addr_id     = p_addr_id 
			 , addr_detail = p_street_info
			 , country_id  = decode ( v_country_id, 0, country_id, v_country_id)
		WHERE  user_id = p_user_id ;
	
		v_ct := SQL%ROWCOUNT;
		
		IF v_ct = 0 THEN 
			ROLLBACK;
			p_flag := 1;
			
			p_ret_msg := '用户（ID：'||p_user_id||'）不存在！';
			p_data_msg  := '{"flag": '||p_flag||',"message": "'|| p_ret_msg||'" }';
		ELSE 
			COMMIT;
			p_flag := 0;
			
			p_ret_msg := '成功修改用户地址信息';
			p_data_msg  := '{"flag": '||p_flag||',"message": "'|| p_ret_msg||'" }';
		
		END IF;
		
		RETURN;
	EXCEPTION
			
		WHEN OTHERS  THEN 
			p_flag := -2 ;
			p_ret_msg := '修改用户地址失败，未知错误：'|| replace( sqlerrm(), '"', '');
			p_data_msg := NULL;
			p_data_msg  := '{"flag": '||p_flag||',"message": "'|| p_ret_msg||'" }';
			
	END modify_user_addr  ;

   PROCEDURE fetch_child_addr  (
       p_flag          OUT   PLS_INTEGER
	  ,p_ret_msg       OUT   VARCHAR2 
	  ,p_child_addr    OUT   SYS_REFCURSOR      -- can be null
      ,p_parent_id           PLS_INTEGER  -- parent address id 
      )
	IS
	    v_flag         PLS_INTEGER := 0 ;
		v_msg          VARCHAR2(4000);	
		v_parent_id    PLS_INTEGER := nvl( p_parent_id , 0);
		
		v_ct             PLS_INTEGER := 0 ;
		
	BEGIN
	
		pkg_debug.debug_log( $$PLSQL_UNIT, $$PLSQL_LINE,
							'p_parent_id :'||p_parent_id 
							);
		
	
		SELECT COUNT(1) INTO v_ct
		FROM addr_code 
		WHERE parent_id = v_parent_id ;
		
		IF v_ct = 0 THEN 
		
			p_flag := 1 ;
			p_ret_msg  := '该地址（ID：'||p_parent_id ||')下无任何下级地址' ;
			OPEN p_child_addr 
			FOR SELECT ' ' 
			FROM DUAL 
			WHERE NULL IS NOT NULL ;
			
		ELSE 
		
			OPEN p_child_addr 
			FOR SELECT '{"addr_id":"'||addr_id||
			          '","name":"'||name||
					  '","py_inits":"'|| py_initials||
					  '","admin_div_code":"'||admin_div_code||
					  '","area_code":"'||area_code||
					  '"}'  adr1 
			FROM addr_code
			WHERE parent_id=v_parent_id ;
		
			p_flag := 0;
			
			p_ret_msg := '成功获取下级地址信息';
		END IF ;
		pkg_debug.debug_log( $$PLSQL_UNIT, $$PLSQL_LINE,
							'p_flag :'||p_flag  ||
						   ',p_ret_mag :'||p_ret_msg  
							);
		
		RETURN;
	EXCEPTION
		WHEN NO_DATA_FOUND  THEN 
			p_flag :=  1;
			p_ret_msg  := '该地址ID无下一级地址';

			OPEN p_child_addr 
			FOR SELECT ' ' 
			FROM DUAL 
			WHERE NULL IS NOT NULL ;
						
			pkg_debug.debug_log( $$PLSQL_UNIT, $$PLSQL_LINE,
								'p_flag :'||p_flag  ||
							   ',p_ret_mag :'||p_ret_msg  
								);
					
		WHEN OTHERS  THEN 
			p_flag := -2 ;
			p_ret_msg := '查询失败，未知错误：'|| replace( sqlerrm(), '"', '');
			
			OPEN p_child_addr 
			FOR SELECT ' ' 
			FROM DUAL 
			WHERE NULL IS NOT NULL ;
						
			pkg_debug.debug_log( $$PLSQL_UNIT, $$PLSQL_LINE,
								'p_flag :'||p_flag  ||
							   ',p_ret_mag :'||p_ret_msg  
								);
			
	END fetch_child_addr  ;



   PROCEDURE fetch_child_addr  (
       p_flag          OUT   PLS_INTEGER
	  ,p_ret_msg       OUT   VARCHAR2 
	  ,p_data_msg      OUT   CLOB     -- can be null
      ,p_parent_id           PLS_INTEGER  -- parent address id 
      )
	IS
	    v_flag         PLS_INTEGER := 0 ;
		v_msg          VARCHAR2(4000);	
		v_parent_id    PLS_INTEGER := nvl( p_parent_id , 0);
		
	BEGIN
	
	
		SELECT 
			   '"data":['||
					listagg(  '{"addr_id":"'      || addr_id          ||'",'||
							   '"name":"'         || name             ||'",'||
--							   '"py_inits":"'     || py_initial       ||'",'||	
							   '"py_inits":"'     || py_initials      ||'",'||
							   '"py_full":"'      || py_full          ||'",'||
							   '"area_code":"'    || area_code        ||'"' ||
							                        '}' , ',' )                            
						  WITHIN GROUP ( order by addr_id  )
					  ||'] '      
	    INTO v_msg 				  
		FROM addr_code 
		WHERE parent_id  = v_parent_id; 

		
		p_flag := 0;
		
		p_ret_msg := '成功获取下级地址信息';
		p_data_msg  := '{"flag": '||p_flag||',"message": "'|| p_ret_msg||'",'||v_msg ||'}';
		
		RETURN;
	EXCEPTION
		WHEN NO_DATA_FOUND  THEN 
			p_flag :=  -1;
			p_ret_msg  := '查询失败，该地址ID无下一级地址';
			p_data_msg  :=  '{ "flag": '||p_flag
			              ||' ,"message": "'|| p_ret_msg ||'"'
						  ||' ,"data":[] }';
			
		WHEN OTHERS  THEN 
			p_flag := -2 ;
			p_ret_msg := '查询失败，未知错误：'|| replace( sqlerrm(), '"', '');
			p_data_msg := NULL;
			p_data_msg  :=  '{ "flag": '||p_flag
			              ||' ,"message": "'|| p_ret_msg ||'"'
						  ||' ,"data":[] }';
			
	END fetch_child_addr  ;

	


-- return device_id list for the current user, seperated by ' : '
   PROCEDURE Fetch_Device_List  (
       p_flag          OUT   PLS_INTEGER
	  ,p_ret_msg       OUT   VARCHAR2 
	  ,p_data_msg      OUT   VARCHAR2     -- can be null
      ,p_user_id           PLS_INTEGER  -- parent address id 
      )
	IS                  
	    v_flag          PLS_INTEGER := 0 ;
		v_msg           VARCHAR2(4000);	
                        
	BEGIN
		IF p_user_id = 0 THEN 
			p_flag := -1 ;
			
			p_ret_msg := '用户ID有误('||p_user_id ||')，请检查该参数取值';
			p_data_msg  := ' null  ';
		
		ELSE 
		
			SELECT  LISTAGG ( device_id, ' : ') WITHIN GROUP ( ORDER BY device_id )
			   INTO v_msg 
			FROM user_info
			INNER JOIN pond_info USING(user_id)
			INNER JOIN device_list USING( pond_id)
			WHERE user_id  = p_user_id; 

			
			p_flag := 0;
			
			p_ret_msg := '成功获取用户信息';
			p_data_msg  := v_msg ;
		END IF ;
		
		RETURN;
	EXCEPTION
		WHEN NO_DATA_FOUND  THEN 
			p_flag :=  -1;
			p_ret_msg  := '查询失败，该用户ID不存在';
			p_data_msg  :=  ' null '  ;
			
		WHEN OTHERS  THEN 
			p_flag := -2 ;
			p_ret_msg := '查询失败，未知错误：'|| replace( sqlerrm(), '"', '');
			p_data_msg  :=   p_ret_msg ;
			
	END Fetch_Device_List  ;	

	PROCEDURE fetch_user_info  (
       p_flag          OUT   PLS_INTEGER
	  ,p_ret_msg       OUT   VARCHAR2 
	  ,p_data_msg      OUT   VARCHAR2     -- can be null
      ,p_user_id           PLS_INTEGER  -- parent address id 
      )
	IS                  
	    v_flag          PLS_INTEGER := 0 ;
		v_msg           VARCHAR2(4000);	
                        
        v_addr_line1    VARCHAR2(100);
		v_city_name     addr_code.name        %type;
		v_account       USER_INFO.ACCOUNT     %TYPE;
		v_username      USER_INFO.USERNAME    %TYPE;
		v_alert_number  USER_INFO.ALERT_NUMBER%TYPE;
		v_addr_id       USER_INFO.ADDR_ID     %TYPE;
		v_addr_detail   USER_INFO.ADDR_DETAIL %TYPE;
		                
		v_country_id    USER_INFO.country_id  %TYPE;
		v_country_name  geo_countries.country_name %TYPE;
                        
			            
	BEGIN
		IF p_user_id = 0 THEN 
			p_flag := -1 ;
			
			p_ret_msg := '用户ID有误('||p_user_id ||')，请检查该参数取值';
			p_data_msg  := '{"flag": '||p_flag||',"message": "'|| p_ret_msg||'"}';
		
		ELSE 
		
			SELECT   account
					,username
					,alert_number
					,addr_id
					,addr_detail
					,country_id
					,country_name
			   INTO  v_account    
					,v_username
					,v_alert_number
					,v_addr_id
					,v_addr_detail
					,v_country_id
					,v_country_name
			FROM user_info left join geo_countries using (country_id)
			WHERE user_id  = p_user_id; 


			IF v_addr_id > 0 THEN 
					SELECT LISTAGG (name || suffix, '') WITHIN GROUP (ORDER BY LEVEL DESC) --  addr_id, parent_id, name , level
					      ,max(name) KEEP (DENSE_RANK FIRST ORDER BY level )
					  INTO v_addr_line1 , v_city_name 
					  FROM addr_code
				START WITH addr_id = v_addr_id 
				CONNECT BY addr_id = PRIOR parent_id;
			END IF ;
			
			p_flag := 0;
			
			p_ret_msg := '成功获取用户信息';
			p_data_msg  := '{"flag": '||p_flag||',"message": "'|| p_ret_msg||'"'|| 
							',"username":"'	    || v_username     ||'"'||
							',"account":"'	    || v_account      ||'"'||
							',"alert_num":"'	|| v_alert_number ||'"'||
							',"country_id":"'	|| v_country_id   ||'"'||
							',"country_name":"'	|| v_country_name ||'"'||
							',"addr_id":"'	    || v_addr_id      ||'"'||
							',"addr_area":"'	|| v_addr_line1   ||'"'||
							',"addr_detail":"'  || v_addr_detail  ||'"'||
							',"city_name":"'    || v_city_name    ||'"'||
									'}';
		END IF ;
		
		RETURN;
	EXCEPTION
		WHEN NO_DATA_FOUND  THEN 
			p_flag :=  -1;
			p_ret_msg  := '查询失败，该用户ID不存在';
			p_data_msg  :=  '{ "flag": '||p_flag
			              ||' ,"message": "'|| p_ret_msg ||'"} '
						  ;
			
		WHEN OTHERS  THEN 
			p_flag := -2 ;
			p_ret_msg := '查询失败，未知错误：'|| replace( sqlerrm(), '"', '');
			p_data_msg := NULL;
			p_data_msg  :=  '{ "flag": '||p_flag
			              ||' ,"message": "'|| p_ret_msg ||'" }';
			
	END fetch_user_info  ;	

   PROCEDURE Fetch_Continents_List  (
       p_flag          OUT   PLS_INTEGER
	  ,p_ret_msg       OUT   VARCHAR2 
	  ,p_data_msg      OUT   VARCHAR2    
       )
	IS
	    v_flag         PLS_INTEGER := 0 ;
		v_ret_msg         VARCHAR2(3000);	
		v_data_msg        VARCHAR2(3000);	
		v_continent_list  VARCHAR2(2900);
	BEGIN
				
		SELECT LISTAGG (
					 '{"continent_code":"' || continent_code || '", '
				  || '"continent_name":"'  || continent_name || '"}' 
				  , ',')
			   WITHIN GROUP (ORDER BY NULL)
		  INTO v_continent_list 
		  FROM continent_list;

			
			p_flag := 0;
			
			p_ret_msg := '成功获取世界州列表';
			p_data_msg  := '{"flag": '||p_flag||',"message": "'|| p_ret_msg||'"'|| 
							',"data":['||v_continent_list || ']}';
		
		RETURN;
	EXCEPTION
		WHEN NO_DATA_FOUND  THEN 
			p_flag :=  -1;
			p_ret_msg  := '查询失败，没找到数据清单数据库表内容';
			p_data_msg  :=  '{ "flag": '||p_flag
			              ||' ,"message": "'|| p_ret_msg ||'"} '
						  ;
			
		WHEN OTHERS  THEN 
			p_flag := -2 ;
			p_ret_msg := '查询失败，未知错误：'|| replace( sqlerrm(), '"', '');
			p_data_msg := NULL;
			p_data_msg  :=  '{ "flag": '||p_flag
			              ||' ,"message": "'|| p_ret_msg ||'" }';
			
	END Fetch_Continents_List  ;	


   PROCEDURE fetch_countries  (
       p_flag          OUT   PLS_INTEGER
	  ,p_ret_msg       OUT   VARCHAR2 
	  ,p_countries    OUT   SYS_REFCURSOR      -- can be null
      ,p_contientCode        VARCHAR2  
      )
	IS
	    v_flag         PLS_INTEGER := 0 ;
		v_msg          VARCHAR2(4000);	
		v_contientCode VARCHAR2(3):= TRIM(p_contientcode);
		
	BEGIN
			OPEN p_countries 
			FOR 
			SELECT  '{"country_id":"'|| country_id || '"'
				 || ',"country_name":"'|| country_name || '"'              
				 ||'}'
					   AS country
			  FROM  geo_countries
			  WHERE NVL(  v_contientCode, continent_code)  = continent_code
			    AND  country_name IS NOT NULL;
			  
		
			p_flag := 0;
			
			p_ret_msg := '成功获取'||v_contientCode||'国家名称清单';

		RETURN;
	EXCEPTION
					
		WHEN OTHERS  THEN 
			p_flag := -2 ;
			p_ret_msg := '查询失败，未知错误：'|| replace( sqlerrm(), '"', '');
			
			OPEN p_countries 
			FOR SELECT ' ' 
			FROM DUAL 
			WHERE NULL IS NOT NULL ;
						
	END fetch_countries  ;
	
BEGIN
	pkg_debug.SetTopic('PKG_USER_INFO');
			  	
END pkg_user_info;
/
